--EXEC SP_SEARCH_DISTRIBUTOR_SALES 0,0,0,0

ALTER PROC SP_SEARCH_DISTRIBUTOR_SALES( @DistributorId INT, @CustomerId INT, @Product_Id INT, @Year INT )
AS 
BEGIN
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	SET @SQLSTRING = N'select DISTINCT DSO.Distributor_Id, (SELECT C_Name FROM CUSTOMER WHERE Customer_Id = DSO.Distributor_Id ) AS Distributor, DSO.Dist_SalesOrder_Id, DSO.Dist_SalesOrder_No, DSO.Customer_Id,(SELECT C_Name FROM CUSTOMER WHERE Customer_Id = DSO.Customer_Id ) AS Customer, DSO.Customer_PO, CONVERT( VARCHAR, DSO.Actual_Ship_Date, 101) AS Actual_Ship_Date, DSO.STATUS FROM Dist_sales_order DSO, Dist_Line_Item_Table DSLT WHERE DSO.Dist_SalesOrder_Id = DSLT.Dist_SalesOrder_Id'
	SET @PARAMDEFINITION = N'@PRM_DistributorId INT, @PRM_CustomerId INT, @PRM_PRODUCT_Id INT, @PRM_Year INT'

	IF @DistributorId <> 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND Distributor_Id = @PRM_DistributorId '
	END
	IF @CustomerId <> 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND Customer_Id = @PRM_CustomerId '
	END
	IF @Product_Id <> 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND Product_Id = @PRM_PRODUCT_Id '
	END	
	IF @Year <> 0
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND YEAR(SalesOrder_Date) = @PRM_Year '
	END	
	
	--PRINT @SQLSTRING

	EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_DistributorId = @DistributorId, @PRM_CustomerId = @CustomerId, @PRM_PRODUCT_Id = @Product_Id, @PRM_Year = @Year

END